﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class KhachHangDAO : ConnectionDAO
    {
        public static long KtMaKH(long maKH)
        {
            OleDbConnection cN = null;
            long temp = 0;

            try
            {
                cN = Cnect();

                string cmString = @"SELECT KH.MaKH
                                    FROM KHACHHANG KH
                                    WHERE KH.MaKH=@MaKH";

                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@MaKH", OleDbType.Integer);
                thamSo.Value = maKH;
                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();


                while (rDer.Read())
                {
                    if (!rDer.IsDBNull(0))
                        temp = rDer.GetInt32(0);
                    else
                        temp = 0;
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            if (temp == 0)
            {
                return 0;
            }
            return temp;
        }

        public static int ThemKH(KhachHangDTO KH)
        {
            int temp = 1;
            OleDbConnection cN = null;

            try
            {
                cN = Cnect();

                string cmString = @"INSERT INTO KHACHHANG(MaKH,TenKH,MaLoaiKH,CMND,DiaChi) VALUES (@mKH,@tKH,@mLKH,@cMND,@dC)";

                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo;


                thamSo = new OleDbParameter("@mKH", OleDbType.Integer);
                thamSo.Value = KH.MaKH;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@tKH", OleDbType.VarChar);
                thamSo.Value = KH.TenKH;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@mLKH", OleDbType.Integer);
                thamSo.Value = KH.MaLoaiKH.MaLoaiKH;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@cMND", OleDbType.Integer);
                thamSo.Value = KH.CMND;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@dC", OleDbType.VarChar);
                thamSo.Value = KH.DiaChi;
                cM.Parameters.Add(thamSo);

                cM.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                temp = 0;
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return temp;
        }


        public static List<KhachHangDTO> LayDSKH()
        {
            OleDbConnection cN = null;
            List<KhachHangDTO> KH = new List<KhachHangDTO>();

            try
            {
                cN = Cnect();

                string cmString = @"SELECT K.MaKH,K.TenKH,K.MaLoaiKH,K.CMND,K.DiaChi
                                    FROM KHACHHANG K
                                    ORDER BY K.MaKH asc";


                OleDbCommand cM = new OleDbCommand(cmString, cN);



                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    KhachHangDTO newKH = new KhachHangDTO();

                    if (!rDer.IsDBNull(0))
                        newKH.MaKH = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        newKH.TenKH = rDer.GetString(1);
                    if (!rDer.IsDBNull(2))
                        newKH.MaLoaiKH.MaLoaiKH = rDer.GetInt32(2);
                    if (!rDer.IsDBNull(3))
                        newKH.CMND = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        newKH.DiaChi = rDer.GetString(4);


                    KH.Add(newKH);
                }
            }
            catch (Exception ex)
            {
                KH = new List<KhachHangDTO>();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return KH;
        }

        public static List<KhachHangDTO> LayDSKHTheoMPT(long mPT)
        {
            OleDbConnection cN = null;
            List<KhachHangDTO> KH = new List<KhachHangDTO>();

            try
            {
                cN = Cnect();

                string cmString = @"SELECT K.MaKH,K.TenKH,K.MaLoaiKH,K.CMND,K.DiaChi
                                    FROM KHACHHANG K,CHITIET_PT CT_PT,PHIEUTHUE PT
                                    WHERE PT.MaPhieuThue=@mPT AND PT.MaPhieuThue=CT_PT.MaPhieuThue AND CT_PT.MaKH=K.MaKH
                                    ORDER BY K.MaKH asc";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@mPT", OleDbType.Integer);
                thamSo.Value = mPT;

                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    KhachHangDTO newKH = new KhachHangDTO();

                    if (!rDer.IsDBNull(0))
                        newKH.MaKH = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        newKH.TenKH = rDer.GetString(1);
                    if (!rDer.IsDBNull(2))
                        newKH.MaLoaiKH.MaLoaiKH = rDer.GetInt32(2);
                    if (!rDer.IsDBNull(3))
                        newKH.CMND = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        newKH.DiaChi = rDer.GetString(4);


                    KH.Add(newKH);
                }
            }
            catch (Exception ex)
            {
                KH = new List<KhachHangDTO>();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return KH;
        }
    }
}
